
Imports System.Configuration
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
Imports System.Windows.Forms

Imports System.Reflection
Imports System.Collections.Generic
Imports System.Security.Permissions
Imports System.Net
Imports System.Management

Imports System.Security.Cryptography
Imports MySql.Data.MySqlClient

Public Class SQLPersistence
    Implements IDisposable

    Private cn
    Private Transa
    Dim ctError As Integer = 0
    Dim LComited As Boolean
    Dim LConfigCnn As String

    Public erro As Boolean = False
    Public DescError As String
    Private motor As String
    Private ParamNombre()
    Private ParamTipo()
    Private ParamValor()
    Private _Comited As Boolean = True

    Private MyProvider As Provider

    Public _NegTransaccion As String

    Public StoreProcedure As String
    Dim Parametro()
    Dim OledbParametroValue()
    Dim OledbParametroName()

    Dim tipo As Integer

    Dim Catalogo As String
    Dim userid As String
    Dim Password As String
    Dim Ip As String


    Enum TipoSql
        Insert = 0
        Update = 1
        [Select] = 2
        Delete = 3
    End Enum
    Sub ClearOledbParametro()
        OledbParametroValue = Nothing
        OledbParametroName = Nothing
    End Sub
    Public Function GetTransaccion(ByVal Configcnn As String, ByVal Pagina As String, ByVal usuario As String, ByVal session As String) As String
        Dim exec As New DotNetDal.SQLPersistence(True, "")
        Dim rp As New DotNetResponse.SQLPersistence(session)
        Dim Correl As String
        Dim mes As String = Now.Month
        Dim dia As String = Now.Day
        Dim anio As String = Now.Year
        If mes.Length = 1 Then mes = "0" & mes
        If dia.Length = 1 Then dia = "0" & dia
        Dim csql As String = " select correlativo from sys_pk where anio = '" & anio & "' and mes ='" & mes & "' and dia ='" & dia & "'"
        exec.ExecuteDataTable(rp, csql)
        If Not rp.TieneDatos Then
            Correl = 1
            csql = " insert into sys_pk (anio, mes, dia,correlativo) values('" & anio & "','" & mes & "','" & dia & "','" & Correl & "')"
        Else
            Correl = rp.DtTable.Rows(0).Item(0) + 1
            csql = " update sys_pk set correlativo = '" & Correl & "' where anio = '" & anio & "' and mes ='" & mes & "' and dia ='" & dia & "'"
        End If
        exec.ExecuteNonQuery(rp, csql)
        exec.Commit()
        Return anio & mes & dia & Correl
    End Function
    Public Shared Function GetTransaccionID(ByVal Configcnn As String, ByVal session As String) As String
        Dim exec As New DotNetDal.SQLPersistence(True, "")
        Dim rp As New DotNetResponse.SQLPersistence(session)
        Dim Correl As String
        Dim mes As String = Now.Month
        Dim dia As String = Now.Day
        Dim anio As String = Now.Year
        If mes.Length = 1 Then mes = "0" & mes
        If dia.Length = 1 Then dia = "0" & dia
        Dim csql As String = " select correlativo from sys_pk where anio = '" & anio & "' and mes ='" & mes & "' and dia ='" & dia & "'"
        exec.ExecuteDataTable(rp, csql)
        If Not rp.TieneDatos Then
            Correl = 1
            csql = " insert into sys_pk (anio, mes, dia,correlativo) values('" & anio & "','" & mes & "','" & dia & "','" & Correl & "')"
        Else
            Correl = rp.DtTable.Rows(0).Item(0) + 1
            csql = " update sys_pk set correlativo = '" & Correl & "' where anio = '" & anio & "' and mes ='" & mes & "' and dia ='" & dia & "'"
        End If
        exec.ExecuteNonQuery(rp, csql)
        exec.Commit()
        Return anio & mes & dia & "-" & Correl
    End Function
    Public Shared Function GetTransaccionVentas(ByVal Configcnn As String, ByVal session As String) As String
        Dim exec As New DotNetDal.SQLPersistence(True, "")
        Dim rp As New DotNetResponse.SQLPersistence("")
        Dim Correl As String
        Dim mes As String = Now.Month
        Dim dia As String = Now.Day
        Dim anio As String = Now.Year
        If mes.Length = 1 Then mes = "0" & mes
        If dia.Length = 1 Then dia = "0" & dia
        Dim csql As String = " select correlativo from SYS_PK_VENTAS where anio = '" & anio & "' and mes ='" & mes & "' and dia ='" & dia & "'"
        exec.ExecuteDataTable(rp, csql)
        If Not rp.TieneDatos Then
            Correl = 1
            csql = " insert into SYS_PK_VENTAS (anio, mes, dia,correlativo) values('" & anio & "','" & mes & "','" & dia & "','" & Correl & "')"
        Else
            Correl = rp.DtTable.Rows(0).Item(0) + 1
            csql = " update SYS_PK_VENTAS set correlativo = '" & Correl & "' where anio = '" & anio & "' and mes ='" & mes & "' and dia ='" & dia & "'"
        End If
        exec.ExecuteNonQuery(rp, csql)
        exec.Commit()
        Return anio & mes & dia & "-" & Correl
    End Function
    Public Function GetPK(ByVal Configcnn As String, ByVal session As String) As String
        Dim exec As New DotNetDal.SQLPersistence(True, "")
        Dim rp As New DotNetResponse.SQLPersistence(session)
        Dim Correl As String
        Dim mes As String = Now.Month
        Dim dia As String = Now.Day
        Dim anio As String = Now.Year
        If mes.Length = 1 Then mes = "0" & mes
        If dia.Length = 1 Then dia = "0" & dia
        Dim csql As String = " select correlativo from sys_pk where anio = '" & anio & "' and mes ='" & mes & "' and dia ='" & dia & "'"
        exec.ExecuteDataTable(rp, csql)
        If Not rp.TieneDatos Then
            Correl = 1
            csql = " insert into sys_pk (anio, mes, dia,correlativo) values('" & anio & "','" & mes & "','" & dia & "','" & Correl & "')"
        Else
            Correl = rp.DtTable.Rows(0).Item(0) + 1
            csql = " update sys_pk set correlativo = '" & Correl & "' where anio = '" & anio & "' and mes ='" & mes & "' and dia ='" & dia & "'"
        End If
        exec.ExecuteNonQuery(rp, csql)
        exec.Commit()
        Return anio & mes & dia & Correl
    End Function
    Sub AddOlebParametro(ByVal Valor As Object, ByVal nombre As String)
        If OledbParametroValue Is Nothing Then
            ReDim OledbParametroValue(0)
            OledbParametroValue(0) = Valor

            ReDim OledbParametroName(0)
            OledbParametroName(0) = nombre
        Else
            ReDim Preserve OledbParametroValue(OledbParametroValue.Length)
            OledbParametroValue(OledbParametroValue.Length - 1) = Valor

            ReDim Preserve OledbParametroName(OledbParametroName.Length)
            OledbParametroName(OledbParametroName.Length - 1) = nombre
        End If
    End Sub

    Sub AddParametro(ByVal Valor)
        If Parametro Is Nothing Then
            ReDim Parametro(0)
            Parametro(0) = Valor
        Else
            ReDim Preserve Parametro(Parametro.Length)
            Parametro(Parametro.Length - 1) = Valor
        End If
    End Sub
    Public Sub Conectar()
        Try
            cn.Open()
            Transa = cn.BeginTransaction()
            ctError = 0
        Catch ex As Exception

        End Try

    End Sub

    Public Sub ConectarReadUncommitted()
        Try
            cn.Open()
            Transa = cn.BeginTransaction(IsolationLevel.ReadUncommitted)
            ctError = 0
        Catch ex As Exception


        End Try

    End Sub

    Public Sub ConectarReadcommitted()
        Try
            cn.Open()
            Transa = cn.BeginTransaction()
            ctError = 0
        Catch ex As Exception

        End Try

    End Sub

    Public Enum Provider
        SQLOLEDB = 0
        SQLCLIENT = 1
        ORACLIENT = 2
        MysqlClient = 3
    End Enum

    'Sub New(ByVal Comited As Boolean)
    '    LComited = Comited       
    '    Conexion()
    'End Sub

    Sub New(ByVal Comited As Boolean, ByVal Conexion As String, Optional ByVal Proveedor As Provider = SQLPersistence.Provider.SQLCLIENT)

        'If System.Web.HttpContext.Current.Session("usuario") Is Nothing Then
        '    Me.erro = True
        '    Me.DescError = "session del usuario finalizada"
        '    Exit Sub
        'End If

        LComited = Comited
        Try
            MyProvider = Proveedor
            If Proveedor = Provider.MysqlClient Then
                cn = New MySqlConnection
            End If
            If Proveedor = Provider.SQLCLIENT Then
                cn = New SqlConnection
            End If

            If Not cn Is Nothing Then
                If cn.State <> ConnectionState.Closed Then
                    Exit Sub
                End If
            End If

            'cn.ConnectionString = Conexion

            ' Dim arr() As String = KEY_SYS.Key.GetConexion.Split(";")

            cn.ConnectionString = ConfigurationManager.AppSettings(Conexion) '"Data Source=" & arr(3) & ";Initial Catalog=" & arr(0) & ";User Id=" & arr(1) & ";Password=" & arr(2) & ";"


            _Comited = LComited
            If LComited Then
                ConectarReadcommitted()
            Else
                ConectarReadUncommitted()
            End If
        Catch ex As Exception

        End Try



    End Sub
    'Sub Conexion()
    '    Try
    '        MyProvider = Provider.SQLCLIENT
    '        cn = New SqlClient.SqlConnection
    '        If Not cn Is Nothing Then
    '            If cn.State <> ConnectionState.Closed Then
    '                Exit Sub
    '            End If
    '        End If
    '        'Dim arr() As String = KEY_SYS.Key.GetConexion.Split(";")
    '        'LConfigCnn = "Data Source=" & arr(3) & ";Initial Catalog=" & arr(0) & ";User Id=" & arr(1) & ";Password=" & arr(2) & ";"
    '        cn.ConnectionString = "Buscar conexion en Web.config" 'LConfigCnn
    '        _Comited = LComited
    '        If LComited Then
    '            ConectarReadcommitted()
    '        Else
    '            ConectarReadUncommitted()
    '        End If
    '    Catch ex As Exception

    '    End Try


    'End Sub
    'Sub GetParametrosXML()
    '    Dim Arr() As String = KEY_SYS.Key.GetParametros()
    '    Ip = Arr(0)
    '    Catalogo = KEY_SYS.Key.GetIP
    '    userid = Arr(2)
    '    Password = Arr(3)


    'End Sub
    'Public Shared Function GetParametros() As String()
    '    Return KEY_SYS.Key.GetParametros()
    'End Function
    Public Function Executedataset(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String, ByRef Obj() As Object)

        Dim dr
        Try
            Dim paginacion As Integer = Obj(Obj.Length - 1)
            Dim Pagina As Integer = Obj(Obj.Length - 2)
            Dim dt As New DataTable
            dt.TableName = "nodos"

            Dim Row As DataRow
            Dim cell As New DataColumn
            If MyProvider = Provider.SQLOLEDB Then
                dr = OledbHelper.ExecuteReader(DirectCast(Transa, OleDbTransaction), CommandType.Text, Csql)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                dr = SqlClientHelper.ExecuteReader(DirectCast(Transa, SqlTransaction), CommandType.Text, Csql)
            End If

            If MyProvider = Provider.MysqlClient Then
                dr = MySqlClientHelper.ExecuteReader(DirectCast(Transa, MySqlTransaction), CommandType.Text, Csql)
            End If

            Dim i As Integer
            For i = 0 To dr.FieldCount - 1
                dt.Columns.Add(dr.GetName(i))
            Next
            Dim ct As Integer = 1, pag As Integer = 0
            Do While dr.Read
                pag = pag + 1
                If ((paginacion * Pagina) - paginacion) + 1 <= pag And (paginacion * Pagina) >= pag Then
                    Row = dt.NewRow
                    For i = 0 To dr.FieldCount - 1
                        Row(i) = dr.GetValue(i)
                    Next
                    Rp.TieneDatos = True
                    dt.Rows.Add(Row)
                End If
            Loop
            Rp.Ds.Tables.Add(dt)
            dr.Close()

            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If
        Catch ex As Exception
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
                dr.Close()
            Catch exinterno As Exception

            End Try


        End Try

    End Function

    Public Sub ExecuteNonQuery(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If


            If MyProvider = Provider.SQLOLEDB Then
                OledbHelper.ExecuteNonquery(Rp, DirectCast(Transa, OleDbTransaction), Csql)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                SqlClientHelper.ExecuteNonquery(Rp, DirectCast(Transa, SqlTransaction), Csql)
            End If
            If MyProvider = Provider.MysqlClient Then
                MySqlClientHelper.ExecuteNonquery(Rp, DirectCast(Transa, MySqlTransaction), Csql)
            End If


            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If
        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try


        End Try

    End Sub

    'Public Sub ExecuteNonQuery(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Tipo As TipoSql, ByVal Tabla As String, Optional ByVal nombre_Pk As String = "", Optional ByVal Valor_Pk As String = "")
    '    Try
    '        If Me.erro Then
    '            Rp.MensajeError = Me.DescError
    '            Rp.Errores = True
    '            Exit Sub
    '        End If


    '        If MyProvider = Provider.SQLOLEDB Then
    '            If Tipo = TipoSql.Insert Then

    '                Dim comando As OleDbCommand
    '                comando = New OleDbCommand
    '                Dim Sql As String = "INSERT INTO " & Tabla & " ("
    '                Dim Sql2 As String = ""

    '                Dim i As Integer

    '                Sql = Sql & OledbParametroName(0)
    '                Sql2 = "Values (?"

    '                comando.Parameters.AddWithValue("@" & OledbParametroName(0), OledbParametroValue(0))

    '                For i = 1 To OledbParametroName.Length - 1
    '                    Sql = Sql & "," & OledbParametroName(i)
    '                    Sql2 = Sql2 & ",?"

    '                    comando.Parameters.AddWithValue("@" & OledbParametroName(i), OledbParametroValue(i))

    '                Next
    '                Sql = Sql & " )"
    '                Sql2 = Sql2 & " )"

    '                comando.CommandText = Sql & Sql2
    '                comando.CommandType = CommandType.Text
    '                comando.CommandTimeout = 60
    '                comando.Connection = DirectCast(Transa, OleDbTransaction).Connection
    '                comando.Transaction = DirectCast(Transa, OleDbTransaction)
    '                comando.ExecuteNonQuery()
    '                comando.Dispose()


    '                Exit Sub

    '            End If

    '            If Tipo = TipoSql.Update Then
    '                Dim comando As OleDbCommand
    '                comando = New OleDbCommand
    '                Dim Sql As String = "UPDATE " & Tabla & " SET "

    '                Dim i As Integer

    '                Sql = Sql & OledbParametroName(0) & "=@" & OledbParametroName(0)
    '                comando.Parameters.AddWithValue("@" & OledbParametroName(0), OledbParametroValue(0))
    '                For i = 1 To OledbParametroName.Length - 1
    '                    Sql = Sql & "," & OledbParametroName(i) & "=@" & OledbParametroName(i)
    '                    comando.Parameters.AddWithValue("@" & OledbParametroName(i), OledbParametroValue(i))
    '                Next
    '                Sql = Sql & " where " & nombre_Pk & " ='" & Valor_Pk & "'"

    '                comando.CommandText = Sql
    '                comando.CommandType = CommandType.Text
    '                comando.CommandTimeout = 60
    '                comando.Connection = DirectCast(Transa, OleDbTransaction).Connection
    '                comando.Transaction = DirectCast(Transa, OleDbTransaction)
    '                comando.ExecuteNonQuery()
    '                comando.Dispose()

    '            End If

    '            If Tipo = TipoSql.Delete Then
    '                Dim comando As OleDbCommand
    '                comando = New OleDbCommand
    '                Dim Sql As String = "Delete from " & Tabla & " "
    '                Sql = Sql & " where " & nombre_Pk & " ='" & Valor_Pk & "'"

    '                comando.CommandText = Sql
    '                comando.CommandType = CommandType.Text
    '                comando.CommandTimeout = 60
    '                comando.Connection = DirectCast(Transa, OleDbTransaction).Connection
    '                comando.Transaction = DirectCast(Transa, OleDbTransaction)
    '                comando.ExecuteNonQuery()
    '                comando.Dispose()

    '            End If


    '        End If
    '        If MyProvider = Provider.SQLCLIENT Then
    '            'SqlClientHelper.ExecuteNonquery(Rp, DirectCast(Transa, SqlTransaction), Csql)

    '            If Tipo = TipoSql.Insert Then

    '                Dim comando As SqlClient.SqlCommand
    '                comando = New SqlClient.SqlCommand
    '                Dim Sql As String = "INSERT INTO " & Tabla & " ("
    '                Dim Sql2 As String = ""

    '                Dim i As Integer

    '                Sql = Sql & OledbParametroName(0)
    '                Sql2 = "Values ( @" & OledbParametroName(0)

    '                comando.Parameters.AddWithValue("@" & OledbParametroName(0), OledbParametroValue(0))

    '                For i = 1 To OledbParametroName.Length - 1
    '                    Sql = Sql & "," & OledbParametroName(i)
    '                    Sql2 = Sql2 & ",@" & OledbParametroName(i)

    '                    comando.Parameters.AddWithValue("@" & OledbParametroName(i), OledbParametroValue(i))

    '                Next
    '                Sql = Sql & " )"
    '                Sql2 = Sql2 & " )"

    '                comando.CommandText = Sql & Sql2
    '                comando.CommandType = CommandType.Text
    '                comando.CommandTimeout = 60
    '                comando.Connection = DirectCast(Transa, SqlClient.SqlTransaction).Connection
    '                comando.Transaction = DirectCast(Transa, SqlClient.SqlTransaction)
    '                comando.ExecuteNonQuery()
    '                comando.Dispose()


    '                Exit Sub

    '            End If

    '            If Tipo = TipoSql.Update Then

    '                Dim comando As SqlClient.SqlCommand
    '                comando = New SqlClient.SqlCommand
    '                Dim Sql As String = "UPDATE " & Tabla & " SET "

    '                Dim i As Integer


    '                Sql = Sql & OledbParametroName(0) & "=@" & OledbParametroName(0)
    '                comando.Parameters.AddWithValue("@" & OledbParametroName(0), OledbParametroValue(0))
    '                For i = 1 To OledbParametroName.Length - 1
    '                    Sql = Sql & "," & OledbParametroName(i) & "=@" & OledbParametroName(i)
    '                    comando.Parameters.AddWithValue("@" & OledbParametroName(i), OledbParametroValue(i))
    '                Next
    '                Sql = Sql & " where " & nombre_Pk & " ='" & Valor_Pk & "'"

    '                comando.CommandText = Sql
    '                comando.CommandType = CommandType.Text
    '                comando.CommandTimeout = 60
    '                comando.Connection = DirectCast(Transa, SqlClient.SqlTransaction).Connection
    '                comando.Transaction = DirectCast(Transa, SqlClient.SqlTransaction)
    '                comando.ExecuteNonQuery()
    '                comando.Dispose()

    '            End If

    '            If Tipo = TipoSql.Delete Then

    '                Dim comando As SqlClient.SqlCommand
    '                comando = New SqlClient.SqlCommand
    '                Dim Sql As String = "Delete from " & Tabla & " "
    '                Sql = Sql & " where " & nombre_Pk & " ='" & Valor_Pk & "'"

    '                comando.CommandText = Sql
    '                comando.CommandType = CommandType.Text
    '                comando.CommandTimeout = 60
    '                comando.Connection = DirectCast(Transa, SqlClient.SqlTransaction).Connection
    '                comando.Transaction = DirectCast(Transa, SqlClient.SqlTransaction)
    '                comando.ExecuteNonQuery()
    '                comando.Dispose()

    '            End If

    '        End If

    '        If Not Rp Is Nothing Then
    '            If Rp.Errores Then
    '                erro = True
    '            End If
    '        End If
    '    Catch ex As Exception
    '        'GrabarLog(ex)
    '        erro = True
    '        Try
    '            Rp.TieneDatos = False
    '            Rp.Errores = True
    '            Rp.MensajeError = ex.Message
    '        Catch exinterno As Exception

    '        End Try


    '    End Try

    'End Sub

    Public Sub ExecuteNonquery(ByRef Rp As DotNetResponse.SQLPersistence)

        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                OledbHelper.ExecuteNonquery(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, False, Rp.Parametro)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                SqlClientHelper.ExecuteNonquery(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure, False, Rp.Parametro)
            End If

            If MyProvider = Provider.MysqlClient Then
                MySqlClientHelper.ExecuteNonquery(DirectCast(Transa, MySqlTransaction), Rp.StoredProcedure, False, Rp.Parametro)
            End If


            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If

        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub


    'Public Sub ExecuteNonquery2(ByRef Rp As DotNetResponse.SQLPersistence)

    '    Try
    '        If Me.erro Then
    '            Rp.MensajeError = Me.DescError
    '            Rp.Errores = True
    '            Exit Sub
    '        End If

    '        If MyProvider = Provider.SQLOLEDB Then
    '            OledbHelper.ExecuteNonquery(DirectCast(Transa, OleDbTransaction), Me.StoreProcedure, False, Me.Parametro)
    '        End If
    '        If MyProvider = Provider.SQLCLIENT Then
    '            SqlClientHelper.ExecuteNonquery(DirectCast(Transa, SqlTransaction), Me.StoreProcedure, False, Me.Parametro)
    '        End If


    '        If MyProvider = Provider.SQLCLIENT Then
    '            MySqlClientHelper.ExecuteNonquery(DirectCast(Transa, MySqlTransaction), Me.StoredProcedure, False, Rp.Parametro)
    '        End If


    '        If Not Rp Is Nothing Then
    '            If Rp.Errores Then
    '                erro = True
    '            End If
    '        End If

    '    Catch ex As Exception
    '        'GrabarLog(ex)
    '        erro = True
    '        Try
    '            Rp.TieneDatos = False
    '            Rp.Errores = True
    '            Rp.MensajeError = ex.Message
    '        Catch exinterno As Exception

    '        End Try

    '    End Try
    'End Sub

    Public Sub ExecuteScalar(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.GetScalar = OledbHelper.ExecuteScalar(DirectCast(Transa, OleDbTransaction), Csql)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                SqlClientHelper.ExecuteScalar(Rp, DirectCast(Transa, SqlTransaction), Csql)
            End If

            If MyProvider = Provider.SQLCLIENT Then
                Rp.GetScalar = MySqlClientHelper.ExecuteScalar(DirectCast(Transa, MySqlTransaction), Csql)
            End If



            If Not Rp Is Nothing Then
                If Rp.GetScalar Is Nothing Then
                    Rp.TieneDatos = False
                Else
                    Rp.TieneDatos = True
                End If

                If Rp.Errores Then
                    erro = True
                End If
            End If


        Catch ex As Exception
            '  GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try

    End Sub

    Public Sub ExecuteScalar(ByRef Rp As DotNetResponse.SQLPersistence)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.GetScalar = OledbHelper.ExecuteScalar(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.GetScalar = SqlClientHelper.ExecuteScalar(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If

            If MyProvider = Provider.MysqlClient Then
                Rp.GetScalar = MySqlClientHelper.ExecuteScalar(DirectCast(Transa, MySqlTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If


            If Not Rp Is Nothing Then
                If Rp.GetScalar Is Nothing Then
                    Rp.TieneDatos = False
                Else
                    Rp.TieneDatos = True
                End If

                If Rp.Errores Then
                    erro = True
                End If
            End If


        Catch ex As Exception
            '  GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try

    End Sub


    Public Sub ExecuteDataTable(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.DtTable = OledbHelper.ExecuteDataset(DirectCast(Transa, OleDbTransaction), Csql).Tables(0)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.DtTable = SqlClientHelper.ExecuteDataset(DirectCast(Transa, SqlTransaction), Csql).Tables(0)
            End If
            If MyProvider = Provider.MysqlClient Then
                Rp.DtTable = MySqlClientHelper.ExecuteDataset(DirectCast(Transa, MySqlTransaction), Csql).Tables(0)
            End If


            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If

        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub


    Public Sub ExecuteDataTable(ByRef Rp As DotNetResponse.SQLPersistence)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.DtTable = OledbHelper.ExecuteDataset(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, Rp.Parametro).Tables(0)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.DtTable = SqlClientHelper.ExecuteDataset(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure, Rp.Parametro).Tables(0)
            End If

            If MyProvider = Provider.MysqlClient Then
                Rp.DtTable = MySqlClientHelper.ExecuteDataset(DirectCast(Transa, MySqlTransaction), Rp.StoredProcedure, Rp.Parametro).Tables(0)
            End If

            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If

        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub

    Public Sub ExecuteDataset(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.Ds = OledbHelper.ExecuteDataset(DirectCast(Transa, OleDbTransaction), Csql)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.Ds = SqlClientHelper.ExecuteDataset(DirectCast(Transa, SqlTransaction), Csql)
            End If
            If MyProvider = Provider.MysqlClient Then
                Rp.Ds = MySqlClientHelper.ExecuteDataset(DirectCast(Transa, MySqlTransaction), Csql)
            End If

            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If
        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub

    Public Sub ExecuteDataset(ByRef Rp As DotNetResponse.SQLPersistence)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.Ds = OledbHelper.ExecuteDataset(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If

            If MyProvider = Provider.SQLCLIENT Then
                Rp.Ds = SqlClientHelper.ExecuteDataset(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If

            If MyProvider = Provider.MysqlClient Then
                Rp.Ds = MySqlClientHelper.ExecuteDataset(DirectCast(Transa, MySqlTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If


            If Not Rp Is Nothing Then
                If Rp.Errores Then
                    erro = True
                End If
            End If
        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub

    Public Sub ExecuteReader(ByRef Rp As DotNetResponse.SQLPersistence, ByVal Csql As String)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.GetDataReader = OledbHelper.ExecuteReader(DirectCast(Transa, OleDbTransaction), Csql)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.GetDataReader = SqlClientHelper.ExecuteReader(DirectCast(Transa, SqlTransaction), Csql)
            End If
            If MyProvider = Provider.MysqlClient Then
                Rp.GetDataReader = MySqlClientHelper.ExecuteReader(DirectCast(Transa, MySqlTransaction), Csql)
            End If

            If Not Rp Is Nothing Then
                Rp.TieneDatos = True
                If Rp.Errores Then
                    erro = True
                End If
            End If

        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub
    Public Sub ExecuteReader(ByRef Rp As DotNetResponse.SQLPersistence)
        Try
            If Me.erro Then
                Rp.MensajeError = Me.DescError
                Rp.Errores = True
                Exit Sub
            End If

            If MyProvider = Provider.SQLOLEDB Then
                Rp.GetDataReader = OledbHelper.ExecuteReader(DirectCast(Transa, OleDbTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If
            If MyProvider = Provider.SQLCLIENT Then
                Rp.GetDataReader = SqlClientHelper.ExecuteReader(DirectCast(Transa, SqlTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If

            If MyProvider = Provider.MysqlClient Then
                Rp.GetDataReader = MySqlClientHelper.ExecuteReader(DirectCast(Transa, MySqlTransaction), Rp.StoredProcedure, Rp.Parametro)
            End If

            If Not Rp Is Nothing Then
                Rp.TieneDatos = True
                If Rp.Errores Then
                    erro = True
                End If
            End If


        Catch ex As Exception
            'GrabarLog(ex)
            erro = True
            Try
                Rp.TieneDatos = False
                Rp.Errores = True
                Rp.MensajeError = ex.Message
            Catch exinterno As Exception

            End Try

        End Try
    End Sub

    Public Sub Rollback()
        Try

            Transa.Rollback()
            Close()
        Catch ex As Exception
            Close()
        End Try

    End Sub
    Private Sub Commited()
        If erro = True Then
            Try
                Transa.Rollback()
                Close()
            Catch ex As Exception
            End Try
            Exit Sub
        End If
        Transa.Commit()
    End Sub
    Public Sub Commit(Optional ByVal valor As Boolean = True)

        If erro = True Then
            Try
                Transa.Rollback()
                Close()
            Catch ex As Exception
            End Try
            Exit Sub
        End If
        If Not cn.State = ConnectionState.Open Then
            Exit Sub
        End If


        If _Comited Then
            Transa.Commit()
        End If

    End Sub
    Public Sub Close()
        cn.Close()
    End Sub
    Private disposedValue As Boolean = False  ' To detect redundant calls
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
            End If
        End If
        Me.disposedValue = True
    End Sub




    Public Function Desencriptar(ByVal TextoEncriptar As String) As String

        Dim byKey() As Byte = {}
        Dim IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
        Dim inputByteArray(TextoEncriptar.Length) As Byte
        Dim Clave As String = "P@ssw0rd"


        byKey = System.Text.Encoding.UTF8.GetBytes(Clave)
        Dim des As New DESCryptoServiceProvider
        inputByteArray = Convert.FromBase64String(TextoEncriptar)
        Dim ms As New MemoryStream
        Dim cs As New CryptoStream(ms, des.CreateDecryptor(byKey, IV), CryptoStreamMode.Write)
        cs.Write(inputByteArray, 0, inputByteArray.Length)
        cs.FlushFinalBlock()
        Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8
        Return encoding.GetString(ms.ToArray())


    End Function


#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Close()
        Dispose(True)
        GC.SuppressFinalize(Me)
        GC.Collect()
    End Sub
#End Region

End Class
